package com.sciencep.pro.common.utils;

import com.sciencep.pro.common.exception.CustomException;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
@Slf4j
public class ExcelUtil {
    public static final String XLS = ".xls";
    public static final String XLSX = ".xlsx";

    public <T> List<T> readExcelFileToDTO(MultipartFile file, Class<T> clazz) throws IOException, CustomException {
        return readExcelFileToDTO(file, clazz, 0);
    }

    public <T> List<T> readExcelFileToDTO(MultipartFile file, Class<T> clazz, Integer sheetId) throws IOException, CustomException {
        //将文件转成workbook类型
        Workbook workbook = null;
        try {
            workbook = buildWorkbook(file);
        } catch (Exception e) {
            throw new CustomException("读取文件失败");
        }
        //第一个表
        return readSheetToDTO(workbook.getSheetAt(sheetId), clazz);
    }

    public <T> List<T> readSheetToDTO(Sheet sheet, Class<T> clazz) throws CustomException {
        List<T> result = new ArrayList<>();
        List<Map<String, Object>> sheetValue = changeSheetToMapList(sheet);
        for (Map<String, Object> valueMap : sheetValue) {
            T dto = null;
            try {
                dto = buildDTOByClass(clazz, valueMap);
            } catch (ReflectiveOperationException e) {
                e.printStackTrace();
            }
            if (dto != null) {
                result.add(dto);
            }
        }
        return result;
    }

    //类型转换
    private Workbook buildWorkbook(MultipartFile file) throws IOException {
        String filename = file.getOriginalFilename();
        if(filename == null || "".equals(filename)){
            throw new IOException("unknown file format: " + filename);
        }
        if (filename.endsWith(XLS)) {
            return new HSSFWorkbook(file.getInputStream());
        } else if (filename.endsWith(XLSX)) {
            return new XSSFWorkbook(file.getInputStream());
        } else {
            throw new IOException("unknown file format: " + filename);
        }
    }

    private List<Map<String, Object>> changeSheetToMapList(Sheet sheet) throws CustomException {
        List<Map<String, Object>> result = new ArrayList<>();
        int rowNumber = sheet.getPhysicalNumberOfRows();
        // 第一行作为表头
        String[] titles = getSheetRowValues(sheet.getRow(0), 0, true);

        //标题check
        checkTitle(titles);

        for (int i = 1; i < rowNumber; i++) {
            int cellCount = titles.length;

            Object[] values = getSheetRowValues(sheet.getRow(i), cellCount, false);
            if (values == null || values.length == 0) {
                //result.add(new HashMap<>());
                continue;
            }
            int data = 0;
            for(Object str : values){
                if(!StringUtils.isEmpty(str)){
                    data++;
                }
            }
            if(data == 0){
                continue;
            }
            Map<String, Object> valueMap = new HashMap<>();
            for (int j = 0; j < titles.length; j++) {
                valueMap.put(titles[j], values[j]);
            }
            result.add(valueMap);
        }
        return result;
    }

    private <T> T buildDTOByClass(Class<T> clazz, Map<String, Object> valueMap) throws IllegalAccessException, InstantiationException {
        int count = 0;

            T dto = clazz.newInstance();
            for (Field field : clazz.getDeclaredFields()) {
                ApiModelProperty desc = field.getAnnotation(ApiModelProperty.class);
                Object value = valueMap.get(desc.value());
                try {
                    if (value != null) {
                        if ("java.lang.Integer".equalsIgnoreCase(field.getType().getName()) && !("".equals(value))) {
                            Method method = clazz.getMethod(getSetMethodName(field.getName()), field.getType());
                            if (value.toString().contains(".")) {
                                Integer intValue = Integer.parseInt(value.toString().substring(0, value.toString().indexOf('.')));
                                method.invoke(dto, intValue);
                                count++;
                            } else {
                                Integer intValue = Integer.parseInt(value.toString());
                                method.invoke(dto, intValue);
                                count++;
                            }
                        } else if ("java.math.BigDecimal".equalsIgnoreCase(field.getType().getName()) && !("".equals(value))) {
                            Method method = clazz.getMethod(getSetMethodName(field.getName()), field.getType());
                            BigDecimal decimalValue = new BigDecimal(value.toString());
                            method.invoke(dto, decimalValue);
                            count++;
                        } else if ("java.lang.String".equalsIgnoreCase(field.getType().getName()) && !("".equals(value))) {
                            Method method = clazz.getMethod(getSetMethodName(field.getName()), field.getType());
                            method.invoke(dto, value);
                            count++;
                        } else {
                            count++;
                        }
                    }
                } catch (Exception e) {
                    log.error("excel解析异常 {}", e);
                }
            }
            if (count == 0) {
                return null;
            }
            return dto;

    }

    private String getSetMethodName(String name) {
        String firstChar = name.substring(0, 1);
        return "set" + firstChar.toUpperCase() + name.substring(1);
    }

    private String[] getSheetRowValues(Row row, int titleCellCount, boolean isTitle) {
        if (row == null) {
            return new String[]{};
        } else {
            int cellNumber;
            if (isTitle) {
                //如果是标题看最后一个单元格位置
                cellNumber = row.getLastCellNum();
            } else {
                //否则按标题的长度来
                cellNumber = titleCellCount;
            }
            List<String> cellValueList = new ArrayList<>();
            for (int i = 0; i < cellNumber; i++) {
                cellValueList.add(getValueOnCell(row.getCell(i)));
            }
            return cellValueList.toArray(new String[0]);
        }
    }

    private String getValueOnCell(Cell cell) {
        if (cell == null) {
            return "";
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if ("yyyy\\-mm\\-dd;@".equals(cell.getCellStyle().getDataFormatString()) || "yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString())
                        || "m/d/yy".equals(cell.getCellStyle().getDataFormatString()) || "yy/m/d".equals(cell.getCellStyle().getDataFormatString())
                        || "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString()) || "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString()) || "yyyy/mm/dd;@".equals(cell.getCellStyle().getDataFormatString())) {
                    return sdf.format(cell.getDateCellValue());
                } else if ("yyyy\\-mm\\-dd\\ h:mm".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy\\-mm\\-dd\\ h:mm:ss".equals(cell.getCellStyle().getDataFormatString())
                        || "m/d/yy h:mm".equals(cell.getCellStyle().getDataFormatString())
                        || "m/d/yy h:mm:ss".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/mm/dd\\ h:mm".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/mm/dd\\ h:mm:ss".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/mm/dd h:mm".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/mm/dd h:mm:ss".equals(cell.getCellStyle().getDataFormatString())) {
                    return simpleDateFormat.format(cell.getDateCellValue());
                } else {
                    cell.setCellType(CellType.STRING);
                    return cell.getStringCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue() ? "true" : "false";
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    return String.valueOf(cell.getNumericCellValue());
                }
            default:
                return "";
        }
    }

    private void checkTitle(String[] titles) throws CustomException {
        boolean userNameFlag = false;
        boolean deptNameFlag = false;
        boolean proNo = false;
        boolean proName = false;
        boolean workDate = false;
        boolean putin = false;
        boolean plan = false;
        boolean projectPoint = false;
        boolean devPoint = false;
        boolean dailyLog = false;
        boolean tomorrowPlan = false;
        boolean dailyRemark = false;
        boolean onDutyTime = false;
        boolean offDutyTime = false;

        for (String str : titles) {
            switch (str) {
                case "用户名称":
                    userNameFlag = true;
                    break;
                case "部门名称":
                    deptNameFlag = true;
                    break;
                case "项目编号":
                    proNo = true;
                    break;
                case "项目名称":
                    proName = true;
                    break;
                case "打卡日期":
                    workDate = true;
                    break;
                case "投入人天":
                    putin = true;
                    break;
                case "绩效人天":
                    plan = true;
                    break;
                case "项目节点":
                    projectPoint = true;
                    break;
                case "开发节点":
                    devPoint = true;
                    break;
                case "今日工作":
                    dailyLog = true;
                    break;
                case "明日工作":
                    tomorrowPlan = true;
                    break;
                case "日志备注":
                    dailyRemark = true;
                    break;
                case "上班打卡时间":
                    onDutyTime = true;
                    break;
                case "下班打卡时间":
                    offDutyTime = true;
                    break;
                default:
                    break;
            }
        }
        if (!userNameFlag) {
            throw new CustomException("第一行,标题:<用户名称>不存在,请检查导入的文件");
        }
        if (!deptNameFlag) {
            throw new CustomException("第一行,标题:<部门名称>不存在,请检查导入的文件");
        }
        if (!proNo) {
            throw new CustomException("第一行,标题:<项目编号>不存在,请检查导入的文件");
        }
        if (!proName) {
            throw new CustomException("第一行,标题:<项目名称>不存在,请检查导入的文件");
        }
        if (!workDate) {
            throw new CustomException("第一行,标题:<打卡日期>不存在,请检查导入的文件");
        }
        if (!putin) {
            throw new CustomException("第一行,标题:<投入人天>不存在,请检查导入的文件");
        }
        if (!plan) {
            throw new CustomException("第一行,标题:<绩效人天>不存在,请检查导入的文件");
        }
        if (!projectPoint) {
            throw new CustomException("第一行,标题:<项目节点>不存在,请检查导入的文件");
        }
        if (!devPoint) {
            throw new CustomException("第一行,标题:<开发节点>不存在,请检查导入的文件");
        }
        if (!dailyLog) {
            throw new CustomException("第一行,标题:<今日工作>不存在,请检查导入的文件");
        }
        if (!tomorrowPlan) {
            throw new CustomException("第一行,标题:<明日工作>不存在,请检查导入的文件");
        }
        if (!dailyRemark) {
            throw new CustomException("第一行,标题:<日志备注>不存在,请检查导入的文件");
        }
        if (!onDutyTime) {
            throw new CustomException("第一行,标题:<上班打卡时间>不存在,请检查导入的文件");
        }
        if (!offDutyTime) {
            throw new CustomException("第一行,标题:<下班打卡时间>不存在,请检查导入的文件");
        }
    }
}